﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Management;
using System.Security.AccessControl;
using System.Text;
using centraldba_library.Data;

namespace centraldba_library.Admin
{
	public class ServerInventoryEventArgs : EventArgs
	{
		public string Message { get; set; }
		public ServerInventoryEventArgs(string message)
		{
			Message = message;
		}
	}

	public class server_inventory
	{
		#region " Fields "

		private string master_connection_string, master_server_name, master_database_name, query_sql_inventory;
		SqlInfoMessageEventHandler sqlMessageHandler;

		public event SqlInfoMessageEventHandler InfoMessage;
		public event EventHandler ProcessComplete;
		public event EventHandler Changed;
		public event EventHandler Changing;
		public event EventHandler Error;
		
		#endregion

		#region " Properties "

		public string MSXConnectionString
		{
			get { return master_connection_string.Replace("yourserver", master_server_name).Replace("centraldba_db", master_database_name); }
		}

		#endregion

		#region " Constructor "

		public server_inventory(string connection_string, string server_name, string database_name)
		{
			master_connection_string = connection_string;
			master_server_name = server_name;
			master_database_name = database_name;

			
			// Validate a server name that isn't "yourserver"
			if (master_server_name.Equals("yourserver"))
				throw new Exception("The master_server_name has not been set.");

			// Validate Connection String template is in order
			bool validString = true;

			if (!master_connection_string.Contains("Data Source=yourserver"))
				validString = false;

			if (!master_connection_string.Contains("Initial Catalog=centraldba_db"))
				validString = false;

			if (!validString)
				throw new Exception("The setting centraldba_db_template has been tampered with.  Please do not change [Data Source=yourserver] or [Initial Catalog=centraldba_db].");

			// Get the inventory query from MSX
			query_sql_inventory = getInventoryQuery();
		}
		public server_inventory(string connection_string, string server_name, string database_name, SqlInfoMessageEventHandler eventHandler)
		{
			master_connection_string = connection_string;
			master_server_name = server_name;
			master_database_name = database_name;
			sqlMessageHandler = eventHandler;

			// Validate a server name that isn't "yourserver"
			if (master_server_name.Equals("yourserver"))
				throw new Exception("The master_server_name has not been set.");

			// Validate Connection String template is in order
			bool validString = true;

			if (!master_connection_string.Contains("Data Source=yourserver"))
				validString = false;

			if (!master_connection_string.Contains("Initial Catalog=centraldba_db"))
				validString = false;

			if (!validString)
				throw new Exception("The setting centraldba_db_template has been tampered with.  Please do not change [Data Source=yourserver] or [Initial Catalog=centraldba_db].");

			// Get the inventory query from MSX
			query_sql_inventory = getInventoryQuery();
		}

		#endregion

		#region " Events "

		protected virtual void OnError(ServerInventoryEventArgs e)
		{
			if (Error != null)
			{
				Error(this, e);
			}
		}
		protected virtual void OnChanging(ServerInventoryEventArgs e)
		{
			if (Changing != null)
			{
				Changing(this, e);
			}
		}
		protected virtual void OnChanged(ServerInventoryEventArgs e)
		{
			if (Changed != null)
			{
				Changed(this, e);
			}
		}
		protected virtual void OnInfoMessage(SqlInfoMessageEventArgs e)
		{
			if (InfoMessage != null)
			{
				InfoMessage(this, e);
			}
		}
		protected virtual void OnProcessComplete()
		{
			if (ProcessComplete != null)
			{
				ProcessComplete(this, null);
			}
		}

		#endregion

		#region " Methods "

		public void run_sqlserver_inventory()
		{
			run_sqlserver_inventory(String.Empty);
		}
		public void run_sqlserver_inventory(string workstation_regex_pattern)
		{
			// Declare Local Variables
			string serverName;
			SqlCommand cm = null;
			SqlDataReader sdr = null;
			System.Data.DataTable dt = null;

			// Init Local Variables
			serverName = String.Empty;

			try
			{
				// Get the list of inventoried servers
				dt = new System.Data.DataTable();
				cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(master_server_name, master_database_name), "[dbo].[spMSX_get_servers_inventory_compare]", System.Data.CommandType.StoredProcedure);
				dt.Load(cm.ExecuteReader());

				// Loop through discovered SQL Servers
				foreach (System.Data.DataRow dr in System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources().Rows)
				{
					serverName = dr["ServerName"].ToString().Trim();

					if (!serverName.Length.Equals(0) && is_server(serverName, workstation_regex_pattern))
					{
						dt.DefaultView.RowFilter = "ssd_sqlserver_name_ro = '" + serverName + "' AND ssd_is_disabled = 0 AND ssd_is_vendor_managed_ro = 0";

						if (!dt.DefaultView.Count.Equals(0))
						{
							OnChanging(new ServerInventoryEventArgs("[run_sqlserver_inventory] " + serverName + " started at " + DateTime.Now.ToLongTimeString()));
							inventory_server_sqlservices(serverName);
							OnChanged(new ServerInventoryEventArgs("[run_sqlserver_inventory] " + serverName + " completed at " + DateTime.Now.ToLongTimeString()));

                            dt.DefaultView[0]["updated"] = true;
						}
					}
				}

                dt.DefaultView.RowFilter = "updated = 0 AND ssd_is_disabled = 0 AND ssd_is_vendor_managed_ro = 0";
                foreach (System.Data.DataRowView drv in dt.DefaultView)
                {
                    serverName = drv["ssd_sqlserver_name_ro"].ToString();

                    OnChanging(new ServerInventoryEventArgs("[run_sqlserver_inventory] " + serverName + " started at " + DateTime.Now.ToLongTimeString()));
                    inventory_server_sqlservices(serverName);
                    OnChanged(new ServerInventoryEventArgs("[run_sqlserver_inventory] " + serverName + " completed at " + DateTime.Now.ToLongTimeString()));

                    drv["updated"] = true;
                }

				// Query previously discovered SQL Servers that have not been updated within the last 24 hours
				cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(master_server_name, master_database_name), "[dbo].[spMSX_get_servers_needing_reinventory]", System.Data.CommandType.StoredProcedure);
				sdr = cm.ExecuteReader();

				while (sdr.Read())
				{
					serverName = sdr.GetString(0);

					if (is_server(serverName, workstation_regex_pattern))
					{
						OnChanging(new ServerInventoryEventArgs("[run_sqlserver_inventory] " + serverName + " started at " + DateTime.Now.ToLongTimeString()));
						inventory_server_sqlservices(serverName);
						OnChanged(new ServerInventoryEventArgs("[run_sqlserver_inventory] " + serverName + " completed at " + DateTime.Now.ToLongTimeString()));
					}
				}
			}
			catch (Exception ex) 
			{
				OnError(new ServerInventoryEventArgs("[run_sqlserver_inventory] " + serverName + " ERROR:  " + ex.Message.ToString()));
			}
			finally
			{
				SqlHelper.CloseConnection(cm, sdr);
				dt.Dispose();
			}
		}

		public void rebuild_central_server_list()
		{
			rebuild_central_server_list(String.Empty);
		}
		public void rebuild_central_server_list(string additionalMsxServers)
		{
			// Declare Local Variables
			SqlCommand cm = null;

			// Update the default MSX server
			try
			{
				OnChanging(new ServerInventoryEventArgs("[rebuild_central_server_list] " + master_server_name + " started at " + DateTime.Now.ToLongTimeString()));

				// Rebuild the Central Server List
				cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(master_server_name, master_database_name), "[dbo].[spMSX_rebuild_central_server_list]", System.Data.CommandType.StoredProcedure);
				cm.ExecuteNonQuery();

				OnChanged(new ServerInventoryEventArgs("[rebuild_central_server_list] " + master_server_name + " completed at " + DateTime.Now.ToLongTimeString()));
			}
			catch (Exception ex) 
			{
				OnError(new ServerInventoryEventArgs("[rebuild_central_server_list] " + master_server_name + " ERROR:  " + ex.Message.ToString()));
			}
			finally
			{
				SqlHelper.CloseConnection(cm);
			}

			// Update Additional MSX Servers when requested
			if (!additionalMsxServers.Equals(String.Empty))
			{
				foreach (string serverName in additionalMsxServers.Split(",".ToCharArray()))
				{
					try
					{
						OnChanging(new ServerInventoryEventArgs("[rebuild_central_server_list] " + serverName + " started at " + DateTime.Now.ToLongTimeString()));

						// Copy the primary MSX data to the secondary MSX server
						cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spMSX_update_backup_msx_server]", System.Data.CommandType.StoredProcedure);
						cm.Parameters.AddWithValue("msx_server_name", master_server_name);
						cm.ExecuteNonQuery();
						SqlHelper.CloseConnection(cm);

						// Rebuild the Central Server List
						cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(serverName, master_database_name), "[dbo].[spMSX_rebuild_central_server_list]", System.Data.CommandType.StoredProcedure);
						cm.ExecuteNonQuery();

						OnChanged(new ServerInventoryEventArgs("[rebuild_central_server_list] " + serverName + " completed at " + DateTime.Now.ToLongTimeString()));
					}
					catch (Exception ex)
					{
						OnError(new ServerInventoryEventArgs("[rebuild_central_server_list] " + serverName + " ERROR:  " + ex.Message.ToString()));
					}
					finally
					{
						SqlHelper.CloseConnection(cm);
					}
				}
			}
		}

		public void inventory_server_sqlservices(string serverName)
		{
			// Declare Local Variables
			string lastAction;
			string name, startName, startMode, state, instanceName;
			string agentName, agentStartName, agentStartMode, agentState;
			string pwrshl_script_dir;
			string conn;
			SqlCommand cm = null;

			OnChanging(new ServerInventoryEventArgs("[inventory_server_sqlservices] " + serverName + " started at " + DateTime.Now.ToLongTimeString()));

			// Init Local Variables
			name = startName = startMode = state = instanceName = String.Empty;
			agentName = agentStartName = agentStartMode = agentState = String.Empty;
			pwrshl_script_dir = lastAction = conn = String.Empty;

			// In case of a named instance, only want the server name as part of a WMI connection string.
			if (serverName.Contains("\\"))
				serverName = serverName.Substring(0, serverName.IndexOf("\\"));
			conn = "\\\\" + serverName + "\\root\\cimv2";

			try
			{
				// Connect to the MSX Server
				lastAction = "Getting SqlCommand for [dbo].[spMSX_update_server]";
				cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(master_server_name, master_database_name), "[dbo].[spMSX_update_server]", System.Data.CommandType.StoredProcedure);

				// Set SqlCommand Parameters
				cm.Parameters.AddWithValue("server_name", serverName);
				cm.Parameters.AddWithValue("is_error", 0);
				cm.Parameters.AddWithValue("sql_service", String.Empty);
				cm.Parameters.AddWithValue("sql_start_mode", String.Empty);
				cm.Parameters.AddWithValue("sql_state", String.Empty);
				cm.Parameters.AddWithValue("agent_service", String.Empty);
				cm.Parameters.AddWithValue("agent_start_mode", String.Empty);
				cm.Parameters.AddWithValue("agent_state", String.Empty);
				cm.Parameters.AddWithValue("instance_name", String.Empty);
				cm.Parameters.AddWithValue("pwrshl_script_dir", String.Empty);

				// Inventory the requested server
				lastAction = "Querying Win32_Service for MSSQL* Services";
				ManagementObjectSearcher qryServices = new ManagementObjectSearcher(conn, "SELECT Name, StartName, StartMode, State FROM Win32_Service WHERE Name LIKE 'MSSQL$%' OR Name = 'MSSQLServer'");
				foreach (ManagementObject service in qryServices.Get())
				{
					// Retrieve MSSQLSERVER Service Properties
					name = service["Name"].ToString();

                    // Do not inventory the Windows Internal Database
                    if (!name.ToUpper().Equals("MSSQL$MICROSOFT##WID"))
                    {
                        startName = service["StartName"].ToString();
                        startMode = service["StartMode"].ToString();
                        state = service["State"].ToString();

                        if (name.ToUpper().Equals("MSSQLSERVER"))
                        {
                            agentName = "SQLSERVERAGENT";
                            instanceName = String.Empty;
                        }
                        else
                        {
                            agentName = name.Replace("MSSQL$", "SQLAGENT$");
                            instanceName = name.Replace("MSSQL$", String.Empty);
                        }

                        // Retrieve SQLAGENT Service Properties
                        lastAction = "Querying Win32_Service for SQLAgent Service [" + agentName + "]";
                        ManagementObjectCollection moc = new ManagementObjectSearcher(conn, "SELECT StartName, StartMode, State FROM Win32_Service WHERE Name = '" + agentName + "'").Get();
                        foreach (ManagementObject sqlAgentService in moc)
                        {
                            agentStartName = service["StartName"].ToString();
                            agentStartMode = service["StartMode"].ToString();
                            agentState = service["State"].ToString();

                            // Only look at the first object (there shouldn't be more than one)
                            break;
                        }
                        moc.Dispose();

                        // Retrieve SQL_POWERSHELL_SCRIPTS environment variable
                        lastAction = "Getting the SQL_POWERSHELL_SCRIPTS environment variable - if set";
                        moc = new ManagementObjectSearcher(conn, "SELECT Name, VariableValue FROM Win32_Environment WHERE Name = 'SQL_POWERSHELL_SCRIPTS'").Get();
                        foreach (ManagementObject envVar in moc)
                        {
                            pwrshl_script_dir = envVar["VariableValue"].ToString();

                            // Only look at the first object (there shouldn't be more than one)
                            break;
                        }
                        moc.Dispose();

                        // Update the MSX Database
                        lastAction = "Updating the server inventory on the MSX server";
                        cm.Parameters["instance_name"].Value = instanceName;
                        cm.Parameters["sql_service"].Value = startName;
                        cm.Parameters["sql_start_mode"].Value = startMode;
                        cm.Parameters["sql_state"].Value = state;
                        cm.Parameters["agent_service"].Value = agentStartName;
                        cm.Parameters["agent_start_mode"].Value = agentStartMode;
                        cm.Parameters["agent_state"].Value = agentState;
                        cm.Parameters["pwrshl_script_dir"].Value = pwrshl_script_dir.Trim();
                        cm.Parameters["is_error"].Value = 0;
                        cm.ExecuteNonQuery();

                        // Inventory the SQL Server
                        if (!startMode.Equals("Disabled"))
                        {
                            inventory_sqlserver(serverName, instanceName);
                        }
                    }
				}
				qryServices.Dispose();
			}
			catch (Exception ex) 
			{
				try
				{
					OnError(new ServerInventoryEventArgs("[inventory_server_sqlservices] " + serverName + " ERROR:  " + ex.Message.ToString()));
					if (!lastAction.Equals(String.Empty))
					{
						OnError(new ServerInventoryEventArgs("   Last action was: " + lastAction));
					}
					
					cm.Parameters["sql_service"].Value = null;
					cm.Parameters["sql_start_mode"].Value = null;
					cm.Parameters["sql_state"].Value = null;
					cm.Parameters["agent_service"].Value = null;
					cm.Parameters["agent_start_mode"].Value = null;
					cm.Parameters["agent_state"].Value = null;
					cm.Parameters["pwrshl_script_dir"].Value = null;
					cm.Parameters["is_error"].Value = 1;
					cm.ExecuteNonQuery();
				}
				catch (Exception ex1)
				{
					OnError(new ServerInventoryEventArgs("[inventory_server_sqlservices] " + serverName + " ERROR:  " + ex1.Message.ToString()));
				}
			}
			finally
			{
				// Close Objects
				SqlHelper.CloseConnection(cm);
			}

			OnChanged(new ServerInventoryEventArgs("[inventory_server_sqlservices] " + serverName + " completed at " + DateTime.Now.ToLongTimeString()));
		}

		public void inventory_sqlserver(string sqlServerName)
		{
			// Declare Local Variables
			string serverName, instanceName;

			// Init Local Variables
			if (sqlServerName.Contains("\\"))
			{
				serverName = sqlServerName.Substring(0, sqlServerName.IndexOf("\\"));
				instanceName = sqlServerName.Replace(serverName, String.Empty).Replace("\\", String.Empty);
			}
			else
			{
				serverName = sqlServerName;
				instanceName = String.Empty;
			}

			inventory_sqlserver(serverName, instanceName);
		}
		public void inventory_sqlserver(string serverName, string instanceName)
		{
			// Declare Local Variables
			string sqlServerName = serverName;
			SqlCommand cm = null;
			SqlCommand cmUpd = null;
			SqlDataReader sdr = null;

			if (!instanceName.Trim().Length.Equals(0))
			{
				sqlServerName += "\\" + instanceName;
			}

			OnChanging(new ServerInventoryEventArgs("[inventory_sqlserver] " + sqlServerName + " started at " + DateTime.Now.ToLongTimeString()));

			try
			{
				// Get the command to inventory the SQL Server
				cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(sqlServerName, "master"), query_sql_inventory, System.Data.CommandType.Text);

				// Perform the inventory
				sdr = cm.ExecuteReader();
				while (sdr.Read())
				{
					// Create the command to update inventory in the MSX Server
					cmUpd = SqlHelper.GetCommand(SqlHelper.GetConnectionString(master_server_name, master_database_name), "[dbo].[spMSX_update_sqlserver]", System.Data.CommandType.StoredProcedure);
					cmUpd.Parameters.AddWithValue("server_name", serverName);
					cmUpd.Parameters.AddWithValue("engine_edition", sdr.GetValue(1));
					cmUpd.Parameters.AddWithValue("edition_id", sdr.GetValue(2));
					cmUpd.Parameters.AddWithValue("product_version", sdr.GetValue(3));
					cmUpd.Parameters.AddWithValue("product_level", sdr.GetValue(4));
					cmUpd.Parameters.AddWithValue("has_master_jobs", sdr.GetValue(5));
					cmUpd.Parameters.AddWithValue("has_local_jobs", sdr.GetValue(6));
					cmUpd.Parameters.AddWithValue("backup_directory", sdr.GetValue(7));
					cmUpd.Parameters.AddWithValue("is_mixed_mode", sdr.GetValue(8));
					cmUpd.Parameters.AddWithValue("has_centraldba_db", sdr.GetValue(9));
					cmUpd.Parameters.AddWithValue("instance_name", instanceName.Trim());
					cmUpd.ExecuteNonQuery();
					cmUpd.Dispose();
				}
			}
			catch (Exception ex) 
			{
				OnError(new ServerInventoryEventArgs("[inventory_sqlserver] " + sqlServerName + " ERROR:  " + ex.Message.ToString()));
			}
			finally
			{
				SqlHelper.CloseConnection(cm, sdr);
			}

			OnChanged(new ServerInventoryEventArgs("[inventory_sqlserver] " + sqlServerName + " completed at " + DateTime.Now.ToLongTimeString()));
		}

        public void register_with_msx(string sqlServerName, bool deployBackups, bool deployIndex)
		{
			// Declare Local Variables
			string serverName, instanceName;

			// Init Local Variables
			if (sqlServerName.Contains("\\"))
			{
				serverName = sqlServerName.Substring(0, sqlServerName.IndexOf("\\"));
				instanceName = sqlServerName.Replace(serverName, String.Empty).Replace("\\", String.Empty);
			}
			else
			{
				serverName = sqlServerName;
				instanceName = String.Empty;
			}

			register_with_msx(serverName, instanceName, deployBackups, deployIndex);
		}
        public void register_with_msx(string serverName, string instanceName, bool deployBackups, bool deployIndex)
		{
			// Declare Local Variables
			SqlCommand cm, cmMSX;
			string conn = "\\\\" + serverName + "\\root\\cimv2";
			string sqlServerName = serverName;

			// Initialize Local Variables
			cm = cmMSX = null;
			if (!instanceName.Trim().Length.Equals(0))
				sqlServerName += "\\" + instanceName;

			OnChanging(new ServerInventoryEventArgs("[register_with_msx] " + sqlServerName + " started at " + DateTime.Now.ToLongTimeString()));

			try
			{
				// Register the target server with the master server
				cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(sqlServerName, master_database_name), "[dbo].[spTSX_register_msx]", System.Data.CommandType.StoredProcedure);
				cm.Parameters.AddWithValue("msx_server_name", master_server_name);
				cm.ExecuteNonQuery();

				// Send the Jobs from the master server to the target server
				cmMSX = SqlHelper.GetCommand(SqlHelper.GetConnectionString(master_server_name, master_database_name), "[dbo].[spMSX_register_tsx]", System.Data.CommandType.StoredProcedure);
                cmMSX.Parameters.AddWithValue("server_name", sqlServerName);
                cmMSX.Parameters.AddWithValue("deploy_backups_maint", deployBackups);
                cmMSX.Parameters.AddWithValue("deploy_index_maint", deployIndex);
                cmMSX.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				OnError(new ServerInventoryEventArgs("[register_with_msx] " + sqlServerName + " ERROR:  " + ex.Message.ToString()));
			}
			finally
			{
				SqlHelper.CloseConnection(cm);
				SqlHelper.CloseConnection(cmMSX);
			}

			OnChanged(new ServerInventoryEventArgs("[register_with_msx] " + sqlServerName + " completed at " + DateTime.Now.ToLongTimeString()));
		}

		/// <summary>
		/// Function:  is_server(serverName, workstationExpression)
		/// Determines if the serverName is a workstation based on the RegEx expression
		/// workstationExpression.
		/// </summary>
		/// <param name="serverName"></param>
		/// <param name="workstationExpression"></param>
		/// <returns></returns>
		public bool is_server(string serverName, string workstationExpression)
		{
			if (workstationExpression.Trim().Equals(String.Empty))
			{
				return true;
			}
			else
			{
				System.Text.RegularExpressions.Regex wkstn = new System.Text.RegularExpressions.Regex(workstationExpression, System.Text.RegularExpressions.RegexOptions.IgnoreCase);
				return (wkstn.Matches(serverName).Count.Equals(0));
			}
		}

		/// <summary>
		/// Function:  getInventoryQuery()
		/// Retrieves the T-SQL Query from the MSX server that should be used to build
		/// the inventory of a SQL Server's settings.
		/// </summary>
		/// <returns></returns>
		public string getInventoryQuery()
		{
			// Declare Local Variables
			SqlCommand cm = null;
			string command = String.Empty;

			try
			{
				// Connect to the MSX Server
				cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(master_server_name, master_database_name), "[dbo].[spMSX_server_info_query]", System.Data.CommandType.StoredProcedure);
				command = cm.ExecuteScalar().ToString();
			}
			catch (Exception ex) 
			{
				OnError(new ServerInventoryEventArgs("[getInventoryQuery] " + master_server_name + " ERROR:  " + ex.Message.ToString()));
			}
			finally
			{
				SqlHelper.CloseConnection(cm);
			}

			return command;
		}

		private string pickBackupServer(string backupServerList)
		{
			// Declare Local Variables
			ulong free1, total1, total_free1, free2, total2, total_free2;
			string unc1, unc2;

			// Validate parameters
			if (backupServerList.Trim().Length.Equals(0))
				throw new Exception("backupServerList is non-zero length is required.");

			// Get the unc list
			string[] uncs = backupServerList.Split(",".ToCharArray());
			int unc_count = uncs.Length;

			// Get the initial unc
			unc1 = uncs[0];
			win32.GetDiskFreeSpaceEx(unc1, out free1, out total1, out total_free1);

			// Find the server path with the most free space
			for (int i = 1; i < unc_count; i++)
			{
				unc2 = uncs[i];

				win32.GetDiskFreeSpaceEx(unc2, out free2, out total2, out total_free2);

				if (free2 > free1)
				{
					unc1 = unc2;
					free1 = free2;
					total1 = total2;
					total_free1 = total_free2;

					OnChanging(new ServerInventoryEventArgs("[pickBackupServer] " + unc1 + " has the most free space (" + (free1 / total1) + ")"));
				}
			}

			// Return the server path with the most free space
			return unc1;
		}

		public void setBackupDirectory(string backupServerList, string environment, string clusterName, string sqlServerName, string sqlServiceUserID, string successNotification, string errorNotification)
		{
			// Declare Local Variables
			SqlCommand cm = null;
			string backupDirectory = String.Empty;

			try
			{
				if (!sqlServiceUserID.Equals("LocalService") && !sqlServiceUserID.Equals("LocalSystem"))
				{
                    // Get the directory to set permissions on for the SQL Service Account (changes if a cluster)
                    if (clusterName.ToString().Equals(String.Empty))
					    backupDirectory = System.IO.Path.Combine(pickBackupServer(backupServerList), environment, sqlServerName.Replace("\\", "_"));
                    else
                        backupDirectory = System.IO.Path.Combine(pickBackupServer(backupServerList), environment, clusterName);
					OnChanging(new ServerInventoryEventArgs("[setBackupDirectory] " + sqlServerName + " started at " + DateTime.Now.ToLongTimeString()));

					// Ensure directory exists
					if (!Directory.Exists(backupDirectory))
					{
						OnChanging(new ServerInventoryEventArgs("[setBackupDirectory] Creating directory " + backupDirectory));
						Directory.CreateDirectory(backupDirectory);
					}

                    // Ensure proper permissions exist
                    OnChanging(new ServerInventoryEventArgs("[setBackupDirectory] Setting Permission on directory " + backupDirectory + " for " + sqlServiceUserID));
					DirectorySecurity dSec = Directory.GetAccessControl(backupDirectory);
					dSec.AddAccessRule(new FileSystemAccessRule(sqlServiceUserID,
																FileSystemRights.FullControl,
																InheritanceFlags.ContainerInherit | InheritanceFlags.ObjectInherit,
																PropagationFlags.None,
																AccessControlType.Allow));
					Directory.SetAccessControl(backupDirectory, dSec);
					dSec = null;

                    // Send Notice of Change
                    OnChanging(new ServerInventoryEventArgs("[setBackupDirectory] Sending notification to " + successNotification));
                    cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(sqlServerName, "msdb"), "[dbo].[sp_send_dbmail]", System.Data.CommandType.StoredProcedure);
                    cm.Parameters.AddWithValue("recipients", successNotification);
                    cm.Parameters.AddWithValue("subject", "SQL Server Backup Directory Permissions");
                    cm.Parameters.AddWithValue("body", "Full Control on " + backupDirectory + " has been granted to [" + sqlServiceUserID + "].");
                    cm.ExecuteNonQuery();

                    // If this belongs to a cluster, change the server's backup directory to the server name now
                    if (!clusterName.ToString().Equals(String.Empty))
                    {
                        backupDirectory = System.IO.Path.Combine(backupDirectory, sqlServerName.Replace("\\", "_"));
                        if (!Directory.Exists(backupDirectory))
                        {
                            OnChanging(new ServerInventoryEventArgs("[setBackupDirectory] Creating directory " + backupDirectory));
                            Directory.CreateDirectory(backupDirectory);
                        }
                    }

                    // Set the SQL Instance Backup Directory
                    OnChanging(new ServerInventoryEventArgs("[setBackupDirectory] Setting Backup Directory for SQL Instance " + sqlServerName + " to " + backupDirectory));
					cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(sqlServerName, master_database_name), "[dbo].[spBkp_SetDirectory]", System.Data.CommandType.StoredProcedure);
					cm.Parameters.AddWithValue("backup_directory_name", backupDirectory);
					cm.ExecuteNonQuery();
					SqlHelper.CloseConnection(cm);

					// Reinventory the database server
					inventory_sqlserver(sqlServerName);
				}
			}
			catch (Exception ex)
			{
				OnError(new ServerInventoryEventArgs("[setBackupDirectory] ERROR:  " + ex.Message.ToString()));

				// Send Notice of Change
				cm = SqlHelper.GetCommand(SqlHelper.GetConnectionString(sqlServerName, "msdb"), "[dbo].[sp_send_dbmail]", System.Data.CommandType.StoredProcedure);
				cm.Parameters.AddWithValue("recipients", errorNotification);
				cm.Parameters.AddWithValue("subject", "SQL Server Backup Directory Permissions");
				cm.Parameters.AddWithValue("body", "Failed to grant Full Control on " + backupDirectory + " to [" + sqlServiceUserID + "].");
				cm.ExecuteNonQuery();
			}
			finally
			{
				SqlHelper.CloseConnection(cm);
			}

			OnChanged(new ServerInventoryEventArgs("[setBackupDirectory] " + sqlServerName + " completed at " + DateTime.Now.ToLongTimeString()));
		}

		public void getServerDeployList(bool includeDeployed, bool includeUndeployed, bool includeTest, bool includeQA, bool includeProd, out SqlCommand dataCommand, out SqlDataReader dataReader)
		{
			dataCommand = null;
			dataReader = null;

			try
			{
				// Get the command to inventory the SQL Server
				dataCommand = SqlHelper.GetCommand(SqlHelper.GetConnectionString(master_server_name, master_database_name), "[dbo].[spMSX_get_deploy_list]", System.Data.CommandType.StoredProcedure);
				if (includeUndeployed && !includeDeployed)
					dataCommand.Parameters.AddWithValue("has_centraldba", 0);
				else if (!includeUndeployed && includeDeployed)
					dataCommand.Parameters.AddWithValue("has_centraldba", 1);
				dataCommand.Parameters.AddWithValue("include_test", includeTest);
				dataCommand.Parameters.AddWithValue("include_qa", includeQA);
				dataCommand.Parameters.AddWithValue("include_prod", includeProd);

				// Perform the inventory
				dataReader = dataCommand.ExecuteReader();
			}
			catch (Exception ex)
			{
				OnError(new ServerInventoryEventArgs("[getServerDeployList] ERROR: " + ex.Message.ToString()));
			}
		}

		#endregion
	}
}
